package com.zr.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zr.dao.SearchDao;
import com.zr.model.Content;
import com.zr.util.GetJDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class SearchDaoImpl implements SearchDao{

	GetJDBCUtil gt = new GetJDBCUtil();
	
	@Override
	public JSONObject getAllBySearch(String value, int page, int pagesize) {
		Connection con = gt.getJDBCConnection();
		
		JSONObject jObject = new JSONObject();
		jObject.put("total", this.getAllTotal(value));
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.cabstract LIKE concat('%',?,'%') ");
		sql.append("OR e_content.ctitle LIKE concat('%',?,'%') ");
		sql.append("OR e_content.ccontent LIKE concat('%',?,'%') ");
		sql.append("OR e_content.cspecial LIKE concat('%',?,'%') ");
		sql.append("LIMIT ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			pst.setString(2, value);
			pst.setString(3, value);
			pst.setString(4, value);
			pst.setInt(5, (page-1)*pagesize);
			pst.setInt(6, pagesize);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			
			jObject.put("rows", jArray);
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jObject;
	}

	@Override
	public JSONObject getTitleBySearch(String value, int page, int pagesize) {
		Connection con = gt.getJDBCConnection();
		
		JSONObject jObject = new JSONObject();
		jObject.put("total", this.getTitleTotal(value));
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.ctitle LIKE concat('%',?,'%')");
		sql.append("LIMIT ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			pst.setInt(2, (page-1)*pagesize);
			pst.setInt(3, pagesize);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			jObject.put("total", jArray.size());
			jObject.put("rows", jArray);
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jObject;
	}

	@Override
	public JSONObject getContentBySearch(String value, int page, int pagesize) {
		Connection con = gt.getJDBCConnection();
		
		JSONObject jObject = new JSONObject();
		jObject.put("total", this.getContentTotal(value));
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.ccontent LIKE concat('%',?,'%') ");
		sql.append("LIMIT ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			pst.setInt(2, (page-1)*pagesize);
			pst.setInt(3, pagesize);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			jObject.put("total", jArray.size());
			jObject.put("rows", jArray);
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jObject;
	}

	@Override
	public JSONObject getAbstractBySearch(String value, int page, int pagesize) {
		Connection con = gt.getJDBCConnection();
		
		JSONObject jObject = new JSONObject();
		jObject.put("total", this.getAbstractTotal(value));
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.cabstract LIKE ('%',?,'%') ");
		sql.append("LIMIT ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			pst.setInt(2, (page-1)*pagesize);
			pst.setInt(3, pagesize);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			jObject.put("total", jArray.size());
			jObject.put("rows", jArray);
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jObject;
	}

	@Override
	public JSONObject getSpecialBySearch(String value, int page, int pagesize) {
		Connection con = gt.getJDBCConnection();
		
		JSONObject jObject = new JSONObject();
		jObject.put("total", this.getSpecialTotal(value));
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.cspecial LIKE ('%',?,'%') ");
		sql.append("LIMIT ?,? ");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			pst.setInt(2, (page-1)*pagesize);
			pst.setInt(3, pagesize);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			jObject.put("total", jArray.size());
			jObject.put("rows", jArray);
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jObject;
	}

	@Override
	public int getAllTotal(String value) {
		Connection con = gt.getJDBCConnection();
		int total = 0;
		JSONObject jObject = new JSONObject();
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.cabstract LIKE concat('%',?,'%') ");
		sql.append("OR e_content.ctitle LIKE concat('%',?,'%') ");
		sql.append("OR e_content.ccontent LIKE concat('%',?,'%') ");
		sql.append("OR e_content.cspecial LIKE concat('%',?,'%') ");
		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			pst.setString(2, value);
			pst.setString(3, value);
			pst.setString(4, value);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			total = jArray.size();
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public int getTitleTotal(String value) {
		Connection con = gt.getJDBCConnection();
		int total = 0;
		JSONObject jObject = new JSONObject();
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.ctitle LIKE concat('%',?,'%')");
		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			total = jArray.size();
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public int getContentTotal(String value) {
		Connection con = gt.getJDBCConnection();
		int total = 0;
		JSONObject jObject = new JSONObject();
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.ccontent LIKE concat('%',?,'%') ");
		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			total = jArray.size();
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public int getAbstractTotal(String value) {
		Connection con = gt.getJDBCConnection();
		int total = 0;
		JSONObject jObject = new JSONObject();
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.cabstract LIKE ('%',?,'%') ");
		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			total = jArray.size();
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public int getSpecialTotal(String value) {
		Connection con = gt.getJDBCConnection();
		int total = 0;
		JSONObject jObject = new JSONObject();
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT * ");
		sql.append("FROM e_content ");
		sql.append("WHERE e_content.cspecial LIKE ('%',?,'%') ");
		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, value);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				content.setCname(rs.getString("cname"));
				jArray.add(content);
			}
			total = jArray.size();
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

}
